package ch.sv7.tool.dbmanager.db.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import ch.sv7.tool.dbmanager.DbManagerException;
import ch.sv7.tool.dbmanager.db.ColumnSchema;
import ch.sv7.tool.dbmanager.db.ColumnType;
import ch.sv7.tool.dbmanager.db.ColumnTypeManager;
import ch.sv7.tool.dbmanager.db.Field;
import ch.sv7.tool.dbmanager.db.Row;
import ch.sv7.tool.dbmanager.db.Table;
import ch.sv7.tool.dbmanager.db.TableSchema;
import ch.sv7.tool.dbmanager.db.TableWriter;

public class SQLTableWriter implements TableWriter {

	private Connection con;

	private boolean deleteTable;

	private boolean overwrite;
	
	private boolean hasPrimaryKeys = true;

	private PreparedStatement insertPstmt;

	private PreparedStatement updatePstmt;

	private PreparedStatement selectPstmt;

	private int updateResult;

	private int insertResult;

	private int deleteResult;

	private int skipResult;

	public SQLTableWriter(Connection con, boolean deleteTable, boolean overwrite) {
		insertPstmt = null;
		updatePstmt = null;
		selectPstmt = null;
		updateResult = 0;
		insertResult = 0;
		deleteResult = 0;
		skipResult = 0;
		this.con = con;
		this.deleteTable = deleteTable;
		this.overwrite = overwrite;
	}

	public void writeHeader(TableSchema schema) throws DbManagerException {
		updateResult = 0;
		insertResult = 0;
		deleteResult = 0;
		skipResult = 0;
		Statement stmt = null;
		hasPrimaryKeys = schema.getPrimaryKeyColumnSchemas().length > 0;
		try {
			if (deleteTable) {
				stmt = con.createStatement();
				deleteResult = stmt.executeUpdate("DELETE FROM " + schema.getFullName());
				System.out.println("Nb of records deleted  : " + deleteResult);
			}
			if (!deleteTable){
				selectPstmt = con.prepareStatement(getSelectSQL(schema));
			}
			if (!deleteTable && hasPrimaryKeys){
				updatePstmt = con.prepareStatement(getUpdateSQL(schema));
			}
			insertPstmt = con.prepareStatement(getInsertSQL(schema));
		} catch (SQLException e) {
			throw new DbManagerException("Unable to create PreparedStatements - " + e.getMessage(), e);
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				throw new DbManagerException("Unable to close statement", e);
			}
		}
	}

	public void writeFooter(TableSchema tableschema) throws DbManagerException {
		System.out.println("Nb of records inserted : " + insertResult);
		System.out.println("Nb of records updated  : " + updateResult);
		System.out.println("Nb of records skipped  : " + skipResult);
		try {
			if (insertPstmt != null)
				insertPstmt.close();
			if (updatePstmt != null)
				updatePstmt.close();
			if (selectPstmt != null)
				selectPstmt.close();
		} catch (SQLException e) {
			throw new DbManagerException("Unable to close statements", e);
		}
	}

	public void writeTable(Table table, TableSchema schema) throws DbManagerException {
		writeHeader(schema);
		System.out.println("Nb of records to write : " + table.size());
		for (int i = 0; i < table.size(); i++) {
			Row row = table.getRowAt(i);
			writeRow(row, schema);
		}

		writeFooter(schema);
	}

	public void writeRow(Row row, TableSchema schema) throws DbManagerException {
		ResultSet rs = null;
		try {
			if (deleteTable){
				/*
				 * No preexistence checks when table has been truncated
				 */
				fillInsertPreparedStatement(insertPstmt, row, schema);
				insertResult += insertPstmt.executeUpdate();
			} else {
				fillSelectPreparedStatement(selectPstmt, row, schema);
				rs = selectPstmt.executeQuery();
				if (rs.next()) {
					if (overwrite && hasPrimaryKeys) {
						fillUpdatePreparedStatement(updatePstmt, row, schema);
						updateResult += updatePstmt.executeUpdate();
					} else {
						skipResult++;
					}
				} else {
					fillInsertPreparedStatement(insertPstmt, row, schema);
					insertResult += insertPstmt.executeUpdate();
				}
			}
		} catch (SQLException e) {
			throw new DbManagerException("Unable to write data to table " + schema.getName() + " - " + e.getMessage(),
					e);
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				throw new DbManagerException("Unable to close statement", e);
			}
		}
	}

	private String getSelectSQL(TableSchema schema) {
		String sql = "SELECT * FROM " + schema.getFullName();
		sql = sql + " WHERE ";
		ColumnSchema[] keys = schema.getPrimaryKeyColumnSchemas();
		if (keys.length == 0){
			/*
			 * If no primary keys in table, we take all columns
			 */
			keys = schema.getColumnSchemas();
		}
		for (int i = 0; i < keys.length; i++) {
			if (i != 0)
				sql = sql + " AND ";
			sql = sql + keys[i].getName() + " = ?";
		}

		return sql;
	}

	private void fillSelectPreparedStatement(PreparedStatement pstmt, Row row, TableSchema schema) throws SQLException {
		pstmt.clearParameters();
		ColumnSchema[] keys = schema.getPrimaryKeyColumnSchemas();
		if (keys.length == 0){
			/*
			 * If no primary keys in table, we take all columns
			 */
			keys = schema.getColumnSchemas();
		}
		for (int j = 0; j < keys.length; j++) {
			ColumnType dataType = keys[j].getType();
			String name = keys[j].getName();
			Field field = row.getField(name);
			ColumnTypeManager.writeToDatabase(pstmt, j + 1, dataType, field.getValue());
		}

	}

	private String getInsertSQL(TableSchema schema) {
		String sql = "INSERT INTO " + schema.getFullName() + " (";
		for (int i = 0; i < schema.getColumnSchemas().length; i++) {
			if (i != 0)
				sql = sql + ", ";
			sql = sql + schema.getColumnSchemas()[i].getName();
		}

		sql = sql + " ) VALUES  ( ";
		for (int i = 0; i < schema.getColumnSchemas().length; i++) {
			if (i != 0)
				sql = sql + ", ";
			sql = sql + "?";
		}

		sql = sql + ")";
		return sql;
	}

	private void fillInsertPreparedStatement(PreparedStatement pstmt, Row row, TableSchema schema) throws SQLException,
			DbManagerException {
		pstmt.clearParameters();
		for (int j = 0; j < schema.getColumnSchemas().length; j++) {
			ColumnType dataType = schema.getColumnSchemas()[j].getType();
			String name = schema.getColumnSchemas()[j].getName();
			Field field = row.getField(name);
			if (field == null)
				throw new DbManagerException("No data for field : " + name + " of table : " + schema.getName());
			ColumnTypeManager.writeToDatabase(pstmt, j + 1, dataType, field.getValue());
		}

	}

	private String getUpdateSQL(TableSchema schema) {
		String sql = "UPDATE " + schema.getFullName() + " SET ";
		for (int i = 0; i < schema.getColumnSchemas().length; i++) {
			if (i != 0)
				sql = sql + ", ";
			sql = sql + schema.getColumnSchemas()[i].getName() + " = ? ";
		}

		sql = sql + " WHERE ";
		for (int i = 0; i < schema.getPrimaryKeyColumnSchemas().length; i++) {
			if (i != 0)
				sql = sql + " AND ";
			sql = sql + schema.getPrimaryKeyColumnSchemas()[i].getName() + " = ?";
		}

		return sql;
	}

	private void fillUpdatePreparedStatement(PreparedStatement pstmt, Row row, TableSchema schema) throws SQLException {
		int index = 1;
		pstmt.clearParameters();
		for (int j = 0; j < schema.getColumnSchemas().length; j++) {
			ColumnType dataType = schema.getColumnSchemas()[j].getType();
			String name = schema.getColumnSchemas()[j].getName();
			Field field = row.getField(name);
			ColumnTypeManager.writeToDatabase(pstmt, index++, dataType, field.getValue());
		}

		for (int j = 0; j < schema.getPrimaryKeyColumnSchemas().length; j++) {
			ColumnType dataType = schema.getPrimaryKeyColumnSchemas()[j].getType();
			String name = schema.getPrimaryKeyColumnSchemas()[j].getName();
			Field field = row.getField(name);
			ColumnTypeManager.writeToDatabase(pstmt, index++, dataType, field.getValue());
		}

	}
}
